import io
import xlwt
from django.db import connection
from django.db.models import Q
from django.http import HttpResponse, HttpRequest, JsonResponse
from django.shortcuts import redirect

from django.utils import timezone
from rest_framework.decorators import api_view

from polls.models import Record
import re
from urllib.parse import quote
from polls.serializer import RecordSerializer
from rest_framework.response import Response


def index(request: HttpRequest) -> HttpResponse:
    return redirect('/static/html/index.html')


@api_view(('GET', ))
def search(request: HttpRequest) -> HttpResponse:
    queryset = Record.objects.filter(is_deleted=False) \
        .defer('is_deleted', 'deleted_time', 'updated_time') \
        .select_related('car').order_by('-makedate')
    carinfo = request.GET.get('carinfo')
    if carinfo:
        carinfo = re.sub(r'\s', '', carinfo)
        queryset = queryset.filter(
            Q(car__carno__istartswith=carinfo) | Q(car__owner__icontains=carinfo)
        )
    # queryset钟包含多条记录，需要加上many=True
    seri = RecordSerializer(queryset, many=True)
    return Response({'records': seri.data})

# def search(request: HttpRequest) -> HttpResponse:
#     page = int(request.GET.get('page', '1'))
#     size = int(request.GET.get('size', '5'))
#     # 一对一、多对一外键关联，可以通过select_related（“关联对象”）解决1+n查询问题
#     # 多对多外键关联，可以通过prefetch_related（“关联对象”）解决1+n查询问题
#     # 通过queryset对象的only指定哪些需要投影的字段
#     # 通过queryset对象的defer指定不需要投影的字段er
#     queryset = Record.objects.filter(is_deleted=False)\
#         .defer('is_deleted', 'deleted_time', 'updated_time')\
#         .select_related('car')
#     carinfo = request.POST.get('carinfo')
#     if carinfo:
#         carinfo = re.sub(r'\s', '', carinfo)
#         queryset = queryset.filter(
#             Q(car__carno__istartswith=carinfo) | Q(car__owner__icontains=carinfo)
#         )
#     count = queryset.count()
#     queryset = queryset.order_by('-makedate')[(page - 1) * size:page * size]
#     total_page = (count - 1) // size + 1
#     return render(request, 'index.html', {
#         'records': queryset,
#         'current_page': page,
#         'page_size': size,
#         'total_page': total_page,
#         'has_prev': page > 1,
#         'has_next': page < total_page,
#         'prev_page': page - 1,
#         'next_page': page + 1,
#         'carinfo': carinfo
#     })


def handle_record(request: HttpRequest) -> HttpResponse:
    try:
        rno = int(request.GET.get('rno'))
        record = Record.objects.filter(no=rno, dealt=False).first()
        if record and not record.dealt:
            record.dealt = True
            dealt = record.dealt
            record.updated_time = timezone.now()
            record.save()
            data = {'code': 30000, 'message': '已受理', 'dealt': dealt}
        else:
            data = {'code': 30001, 'message': '受理失败'}
    except ValueError:
        data = {'code': 30002, 'message': '违章记录编号无效'}
    return JsonResponse(data)


def delete_record(request: HttpRequest) -> HttpResponse:
    try:
        rno = int(request.GET.get('rno'))
    except ValueError:
        data = {'code': 40002, 'message': '违章记录编号无效'}
    else:
        record = Record.objects.filter(no=rno, is_deleted=False).first()
        if record:
            if record.dealt:
                record.is_deleted = True
                record.deleted_time = timezone.now()
                record.save()
                data = {'code': 40000, 'message': '删除成功'}
            else:
                data = {'code': 40001, 'message': '不能删除没有受理的记录'}
        else:
            data = {'code': 40003, 'message': '删除成功'}
    return JsonResponse(data)


def export_excel(request: HttpRequest) -> HttpResponse:
    queryset = Record.objects.filter(is_deleted=False) \
        .defer('is_deleted', 'deleted_time', 'updated_time') \
        .select_related('car').order_by('-makedate')
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('违章记录表')
    titles = ('编号', '车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '是否受理')
    for col_index, title in enumerate(titles):
        sheet.write(0, col_index, title)
    for row_index, record in enumerate(queryset):
        sheet.write(row_index + 1, 0, record.no)
        sheet.write(row_index + 1, 1, record.car.no)
        sheet.write(row_index + 1, 2, record.car.owner)
        sheet.write(row_index + 1, 3, record.reason)
        sheet.write(row_index + 1, 4, record.makedate.strftime('%Y-%m-%d'))
        sheet.write(row_index + 1, 5, record.punish)
        sheet.write(row_index + 1, 6, '已受理' if record.dealt else '未受理')
    buffer = io.BytesIO()
    # byte -- 字节串 b'\xff\es2\x6d' --- io.BytesIO --- 内存区域
    wb.save(buffer)
    # 创建响应头并指定MIME类型
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    # 设置http响应头, attachment --- 下载文件
    # 文件名处理成百分号编码
    filename = quote('违章记录汇总统计.xls')
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp


def get_bar_data(request: HttpRequest) -> HttpResponse:
    with connection.cursor() as cursor:
        cursor.execute('select carno, ifnull(total, 0) as total from tb_car t1 '
                       ' left outer join (select car_id, count(no) as total from '
                       ' tb_record group by car_id) t2 on t1.no = t2.car_id')
        xdata, ydata = [], []
        for row in cursor.fetchall():
            xdata.append(row[0])
            ydata.append(row[1])
    return JsonResponse({'xdata': xdata, 'ydata': ydata})


def this_is_the_final():
    return 0

